package Dao;
import Dao.IUserDao;
import DBUtil.Dbutil;
import user.entity.User;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements IUserDao {
private static ResultSet rs=null;
    @Override
    public boolean addUser(User user){
        String sql="insert into stu_enroll(num,name,sex,grade,major,number,direction,skill,self,password) values (?,?,?,?,?,?,?,?,?,?)";
        String[] ShuJu={user.getNum(),user.getName(),user.getSex(),user.getGrade(),user.getMajor(),user.getNumber(),user.getDirection(),user.getSkill(),user.getSelf(), user.getPassword()};
        return Dbutil.Update(sql,ShuJu);
    }

    @Override
    public boolean deleteUser(String num) {
        String sql="delete from stu_enroll where num=?";
        String[] ShuJu=new String[]{num};
        return Dbutil.Update(sql,ShuJu);
    }

    @Override
    public boolean updateUser(String num, User user) {
        String sql="update stu_enroll set name=?,sex=?,grade=?,major=?,number=?,direction=?,skill=?,self=?,password=? where num=?";
        String[] ShuJu={user.getName(),user.getSex(),user.getGrade(),user.getMajor(),user.getNumber(),user.getDirection(),user.getSkill(),user.getSelf(),user.getPassword(),num};
        return Dbutil.Update(sql,ShuJu);
    }

    @Override
    public List<User> queryAllUser() {
        User user=null;
        //隐藏管理员信息and查询用户信息
        String num1="31200";
        //返回对象集合
        List<User> users=new ArrayList<>();
        try {
            String sql="select *from stu_enroll where num!=?";
            rs= Dbutil.query(sql,num1,null);
            while(rs.next()){
                String num=rs.getString("num");
                String name=rs.getString("name");
                String sex=rs.getString("sex");
                String grade=rs.getString("grade");
                String major=rs.getString("major");
                String number=rs.getString("number");
                String direction=rs.getString("direction");
                String skill=rs.getString("skill");
                String self=rs.getString("self");
                String password=rs.getString("password");
                user=new User(num,name,sex,grade,major,number,direction,skill,self,password);
                users.add(user);
            }
            return users;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            Dbutil.closes(rs,Dbutil.prep,Dbutil.conn);
        }

        return null;
    }

    @Override
    public User queryByNum(String num) {
        User user = null;

        String sql = "select *from stu_enroll where num=?";
        rs = Dbutil.query(sql, num,null);
        try {
            if (rs.next()) {
                String name = rs.getString("name");
                String sex = rs.getString("sex");
                String grade = rs.getString("grade");
                String major = rs.getString("major");
                String number = rs.getString("number");
                String direction = rs.getString("direction");
                String skill = rs.getString("skill");
                String self = rs.getString("self");
                String password=rs.getString("password");
                user = new User(num, name, sex, grade, major, number, direction, skill, self,password);
            }

        return user;
    } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            if(rs!=null){
                try {
                    rs.close();
                } catch (SQLException e) {
                   e.printStackTrace();
                }
            }
        }
    }

    @Override
    public List<User> queryByName(String name) {
        User user = null;
        String num1="31200";
        //考虑到可能出现同名情况
        List<User> users=new ArrayList<>();
        String sql = "select *from stu_enroll where name=? and num!=?";
        rs = Dbutil.query(sql, name,num1);
        try {
            while (rs.next()) {
                String num= rs.getString("num");
                String sex = rs.getString("sex");
                String grade = rs.getString("grade");
                String major = rs.getString("major");
                String number = rs.getString("number");
                String direction = rs.getString("direction");
                String skill = rs.getString("skill");
                String self = rs.getString("self");
                String password=rs.getString("password");
                user = new User(num, name, sex, grade, major, number, direction, skill, self,password);
                users.add(user);
            }

            return users;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            if(rs!=null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @Override
    public List<User> queryByGrade(String grade) {
        User user = null;
        String num1="31200";
        List<User> users=new ArrayList<>();
        String sql = "select *from stu_enroll where grade=? and num!=?";
        rs = Dbutil.query(sql, grade,num1);
        try {
            while (rs.next()) {
                String num= rs.getString("num");
                String name=rs.getString("name");
                String sex = rs.getString("sex");
                String major = rs.getString("major");
                String number = rs.getString("number");
                String direction = rs.getString("direction");
                String skill = rs.getString("skill");
                String self = rs.getString("self");
                String password=rs.getString("password");
                user = new User(num, name, sex, grade, major, number, direction, skill, self,password);
                users.add(user);
            }

            return users;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            if(rs!=null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    @Override
    public boolean IsExist(String num){
        //判断此人是否存在
        if(queryByNum(num)==null)
            return true;
        else
            return false;
    }
}
